package com.eduction.modules.system.dao;

import java.sql.SQLException;
import java.util.List;

import com.eduction.vo.SurveyPointCountVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.eduction.common.dbutil.DbHelper;
import com.eduction.modules.system.entity.SurveyOpinion;

@Repository
public class SurveyOpinionRepository {

    @Autowired
    private DbHelper dbHelper;
    @Autowired
    private SurveyOpinionDao SurveyOpinionDao;

    /**
     * 保存数据
     */
    public void saveSurveyOpinionInfo(List<SurveyOpinion> so) {
        SurveyOpinionDao.save(so);
    }

    /**
     * 查询数据
     *
     * @throws SQLException
     */
    public SurveyOpinion querySurveyOpinionInfo(String expertId, String itemId, String schoolId, String expertType) throws SQLException {
        String sql = "SELECT * FROM t_survey_opinion where s_expert_id = ? and s_item_id = ? and s_school_id = ? and s_type= ?";
        return dbHelper.getBean(sql, SurveyOpinion.class, expertId, itemId, schoolId, expertType);

    }

    /**
     * 查询数据
     *
     * @throws SQLException
     */
    public SurveyOpinion querySurveyOpinionInfo(String itemId, String schoolId, String expertType) throws SQLException {
        String sql = "SELECT * FROM t_survey_opinion where s_item_id = ? and s_school_id = ? and s_type= ? order by s_create_time desc ";
        return dbHelper.getBean(sql, SurveyOpinion.class, itemId, schoolId, expertType);

    }

    /**
     * 删除某专家一级指标分项考察意见表数据
     *
     * @param schoolId
     * @param itemId
     * @param expertId
     * @param type
     * @throws SQLException
     */
    public void deleteAuthIinspectionOpinionss(String schoolId, String itemId, String expertId, int type) throws SQLException {
        String sql = "DELETE FROM t_survey_opinion WHERE s_school_id=? AND s_item_id=? AND s_expert_id=? AND s_type=? ";
        dbHelper.execute(sql, schoolId, itemId, expertId, type);
    }

    /**
     * 查询该学校的数据(汇总)
     */
    public List<SurveyOpinion> queryAllSurveyOpinion(String schoolId, String mode) throws SQLException {
        String sql = "SELECT * FROM t_survey_opinion where s_school_id = ? and s_mode = ? order by s_create_time";
        return dbHelper.getBeanList(sql, SurveyOpinion.class, schoolId, mode);

    }

    /**
     * 查询下级评分
     *
     *
     * @param expectType
     * @param schoolId
     * @param itemId
     * @return
     * @throws SQLException
     */
    public List<SurveyOpinion> queryItemSurveyOpinion(String schoolId, String itemId,String expectType) throws SQLException {
        String sql = "SELECT * FROM t_survey_opinion where s_school_id = ? and s_item_id = ? and s_type=? order by s_mode asc,s_create_time desc";
        return dbHelper.getBeanList(sql, SurveyOpinion.class, schoolId, itemId,expectType);

    }

    public List<SurveyPointCountVo> queryByCount(String expectType) throws SQLException {
        String sql = "SELECT ss.s_school_id,SUM(ss.countA) AS A,SUM(ss.countB) AS B,SUM(ss.countC) AS C FROM (select s_school_id,count(*) AS countA,0 AS countB,0 AS countC  FROM t_survey_opinion so where so.s_key='A' AND so.s_type=? GROUP BY so.s_school_id UNION  select s_school_id,0 AS countA,count(*) AS countB,0 AS countC  FROM t_survey_opinion so where so.s_key='B' AND so.s_type=? GROUP BY so.s_school_id UNION select s_school_id,0 AS countA,0 AS countB,count(*) AS countC  FROM t_survey_opinion so where so.s_key='C' AND so.s_type=? GROUP BY so.s_school_id) ss GROUP BY ss.s_school_id ORDER BY A DESC,B DESC,C DESC";
        return dbHelper.getBeanList(sql, SurveyPointCountVo.class,expectType,expectType,expectType);
    }
}
